﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Configuration;
using System.Web.Script.Serialization;
using Microsoft.ApplicationBlocks.Data;
using EnglishRemix.Services.Model;
using EnglishRemix.Services.Utility;
using Newtonsoft.Json;
using System.Text.RegularExpressions;

using System.Security.Cryptography;
using System.Security.Cryptography.X509Certificates;
using System.Security.Permissions;
using System.Threading.Tasks;
using AlpineSoft;
using System.Diagnostics;
using EnglishRemix.Services.Model.Requests;
using System.Data.SqlClient;
using EnglishRemix.Services.Model.Exceptions;
using EnglishRemix.Services.Constants;
using EnglishRemix.Services.Model.Responses;
using System.IO;

namespace EnglishRemix.Services.Facade
{
    public class VideoFacade
    {
        private static string connStr = ConfigurationManager.ConnectionStrings["SqlConStr"].ConnectionString;
        private static log4net.ILog log = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
        string sqlStr;

        public VideoFacade()
        {
            log.Debug("VideoFacade initialized");
        }


        public PostCommentResponse PostComment(PostCommentRequest postCommentrequest)
        {
            PostCommentResponse response = new PostCommentResponse();
            response.Message = null;
            response.Result = false;
            List<SqlParameter> sqlParam = new List<SqlParameter>();
            string sqlStr = null;
            int toComment = 0;

            sqlParam.Clear();
            DBUtility.AddSqlParam(sqlParam, "@Creator", SqlDbType.BigInt, postCommentrequest.CreatorId);
            DBUtility.AddSqlParam(sqlParam, "@ToComment", SqlDbType.BigInt, postCommentrequest.ToCommentId);
            DBUtility.AddSqlParam(sqlParam, "@UserVideo", SqlDbType.BigInt, postCommentrequest.UserVideoId);

            sqlStr = @"select 
                              c.Id as CommentId,
                              ui.Id as Creator,
                              uuv.Id as UserVideo                                                 
                              from Comment c
                              join UserInfo ui on ui.Id = c.Creator
                              join UserUploadedVideo uuv on uuv.Id = c.UserVideo
                              where c.Creator = @Creator and c.UserVideo = @UserVideo";


            DataSet ds = DBUtility.ExecuteDataset(sqlStr, sqlParam);
            if (DBUtility.hasResult(ds))
            {//Comment exist
                if (postCommentrequest.ToCommentId != null && postCommentrequest.ToCommentId > 0)
                {
                    toComment = Convert.ToInt32(postCommentrequest.ToCommentId);
                }
                else
                {
                    toComment = Convert.ToInt32(ds.Tables[0].Rows[0]["CommentId"]);
                }

                sqlParam.Clear();
                DBUtility.AddSqlParam(sqlParam, "@UserInfo", SqlDbType.BigInt, postCommentrequest.CreatorId);
                DBUtility.AddSqlParam(sqlParam, "@ToComment", SqlDbType.BigInt, toComment);
                DBUtility.AddSqlParam(sqlParam, "@UserVideo", SqlDbType.BigInt, postCommentrequest.UserVideoId);
                DBUtility.AddSqlParam(sqlParam, "@Content", SqlDbType.NVarChar, postCommentrequest.Content);
                DBUtility.AddSqlParam(sqlParam, "@CreatedOn", SqlDbType.DateTime, DateTime.Now.ToString(ConfigurationManager.AppSettings["DateFormat"]));
                DBUtility.AddSqlParam(sqlParam, "@LastModifiedDate", SqlDbType.DateTime, DateTime.Now.ToString(ConfigurationManager.AppSettings["DateFormat"]));
                DBUtility.AddSqlParam(sqlParam, "@LastModifiedBy", SqlDbType.NVarChar, "System");

                sqlStr = @"insert into Comment(Creator,ToComment,UserVideo,Content,CreatedOn,LastModifiedDate,LastModifiedBy) 
                               values(@UserInfo,@ToComment,@UserVideo,@Content,@CreatedOn,@LastModifiedDate,@LastModifiedBy)";
                if (DBUtility.ExecuteNonQuery(sqlStr, sqlParam) == 1)
                {
                    response.Result = true;
                    response.Message = "comment  posted";
                    return response;
                }
                else
                {
                    throw new ServiceException("post comment failed.");
                }
            }
            else
            {//comment doesn't exist and insert new comment
                sqlParam.Clear();
                DBUtility.AddSqlParam(sqlParam, "@UserInfo", SqlDbType.BigInt, postCommentrequest.CreatorId);
                DBUtility.AddSqlParam(sqlParam, "@ToComment", SqlDbType.BigInt, null);
                DBUtility.AddSqlParam(sqlParam, "@UserVideo", SqlDbType.BigInt, postCommentrequest.UserVideoId);
                DBUtility.AddSqlParam(sqlParam, "@Content", SqlDbType.NVarChar, postCommentrequest.Content);
                DBUtility.AddSqlParam(sqlParam, "@CreatedOn", SqlDbType.DateTime, DateTime.Now.ToString(ConfigurationManager.AppSettings["DateFormat"]));
                DBUtility.AddSqlParam(sqlParam, "@LastModifiedDate", SqlDbType.DateTime, DateTime.Now.ToString(ConfigurationManager.AppSettings["DateFormat"]));
                DBUtility.AddSqlParam(sqlParam, "@LastModifiedBy", SqlDbType.NVarChar, "System");

                sqlStr = @"insert into Comment(Creator,ToComment,UserVideo,Content,CreatedOn,LastModifiedDate,LastModifiedBy) 
                               values(@UserInfo,@ToComment,@UserVideo,@Content,@CreatedOn,@LastModifiedDate,@LastModifiedBy)";
                if (DBUtility.ExecuteNonQuery(sqlStr, sqlParam) == 1)
                {
                    response.Result = true;
                    response.Message = "comment  posted";
                }
                else
                {
                    throw new ServiceException("post comment failed.");
                }
            }
            return response;
        }


        public CommentResponse GetCommentByUploadedVideoId(CommentRequest commentRequest)
        {
            CommentResponse response = new CommentResponse();
            if (commentRequest.UploadedVideoId > 0)
            {
                List<SqlParameter> sqlParam = new List<SqlParameter>();
                List<string> userNameList = new List<string>();
                string sqlStr = null;
                //Get Username from UserInfo table based on Creator Id and other comment Infos
                DBUtility.AddSqlParam(sqlParam, "@UserVideo", SqlDbType.BigInt, commentRequest.UploadedVideoId);
                sqlStr = @"select 
                                ui.Name as UserName,
                                c.ToComment as ToComment,
                                c.UserVideo as UserVideo,
                                c.Content as Content 
                                from Comment c
                                join UserInfo ui on c.Creator = ui.Id
                                where UserVideo = @UserVideo";
                DataSet ds = DBUtility.ExecuteDataset(sqlStr, sqlParam);
                log.Debug("Getting coments now...");
                List<CommentInfo> commentInfos = new List<CommentInfo>();
                if (DBUtility.hasResult(ds))
                {
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        CommentInfo commentInfo = new CommentInfo();
                        commentInfo.UserName = dr["UserName"].ToString();
                        if (dr["ToComment"].ToString().Length > 0)
                        {
                            commentInfo.ToCommentId = Convert.ToInt32(dr["ToComment"]);
                        }
                        else
                        {
                            commentInfo.ToCommentId = null;
                        }
                        commentInfo.UserVideoId = Convert.ToInt32(dr["Uservideo"]);
                        commentInfo.Content = dr["Content"].ToString();
                        commentInfos.Add(commentInfo);
                    }
                    response.Comment = commentInfos;
                }
            }
            else
            {
                throw new ArgumentNullException("Video ID must be greater than 0");
            }
            return response;
        }

        //TODO: Average for all rated scores and set to ratedScore??????
        //Add one culumn to record the number of rated 
        public RateUploadedVideoResponse RateUploadedVideo(RateUploadedVideoRequest rateUploadedVideoRequest)
        {
            RateUploadedVideoResponse response = new RateUploadedVideoResponse();
            List<SqlParameter> sqlParam = new List<SqlParameter>();
            DBUtility.AddSqlParam(sqlParam, "@UserInfo", SqlDbType.BigInt, rateUploadedVideoRequest.CreatorId);
            DBUtility.AddSqlParam(sqlParam, "@Id", SqlDbType.BigInt, rateUploadedVideoRequest.VideoId);
            string sqlStrUploadedVideo = @"select * from UserUploadedVideo where UserInfo = @UserInfo and Id = @Id";
            DataSet dsUploadedVideo = DBUtility.ExecuteDataset(sqlStrUploadedVideo, sqlParam);

            if ((DBUtility.hasResult(dsUploadedVideo)) && (rateUploadedVideoRequest.Score > 0))
            {
                sqlParam.Clear();
                DBUtility.AddSqlParam(sqlParam, "@Creator", SqlDbType.BigInt, rateUploadedVideoRequest.CreatorId);
                DBUtility.AddSqlParam(sqlParam, "@UserVideo", SqlDbType.BigInt, rateUploadedVideoRequest.VideoId);
                string sqlStrRating = @"select Id, Score, NumberOfScore from Rating where Creator = @Creator and UserVideo = @UserVideo";
                DataSet dsRating = DBUtility.ExecuteDataset(sqlStrRating, sqlParam);
                //Uploaded video record exist
                if (DBUtility.hasResult(dsRating))
                {//Rating record exist, update the existing one and add total score and NumberOfScore
                    int ratingId = Convert.ToInt32(dsRating.Tables[0].Rows[0]["Id"]);
                    int totalScore = Convert.ToInt32(dsRating.Tables[0].Rows[0]["Score"]);
                    int totalNumberOfScore = Convert.ToInt32(dsRating.Tables[0].Rows[0]["NumberOfScore"]);
                    totalScore = totalScore + rateUploadedVideoRequest.Score;
                    totalNumberOfScore++;
                    sqlParam.Clear();
                    DBUtility.AddSqlParam(sqlParam, "@Id", SqlDbType.BigInt, ratingId);
                    DBUtility.AddSqlParam(sqlParam, "@Score", SqlDbType.BigInt, totalScore);
                    DBUtility.AddSqlParam(sqlParam, "@NumberOfScore", SqlDbType.BigInt, totalNumberOfScore);
                    DBUtility.AddSqlParam(sqlParam, "@LastModifiedDate", SqlDbType.DateTime, DateTime.Now.ToString(ConfigurationManager.AppSettings["DateFormat"]));
                    sqlStr = @"Update Rating set Score = @Score, NumberOfScore = @NumberOfScore, LastModifiedDate = @LastModifiedDate where Id = @Id";
                    if (DBUtility.ExecuteNonQuery(sqlStr, sqlParam) != 1)
                    {
                        throw new ServiceException("Update Rating table failed. VideoId: " + rateUploadedVideoRequest.VideoId + "; Creator: " + rateUploadedVideoRequest.CreatorId);
                    }
                    response.Result = true;
                    response.Message = "Rated Uploaded Video successed!";
                    return response;
                }
                else
                {//No Rating record exist, insert new one
                    sqlParam.Clear();
                    DBUtility.AddSqlParam(sqlParam, "@Creator", SqlDbType.BigInt, rateUploadedVideoRequest.CreatorId);
                    DBUtility.AddSqlParam(sqlParam, "@UserVideo", SqlDbType.BigInt, rateUploadedVideoRequest.VideoId);
                    DBUtility.AddSqlParam(sqlParam, "@Score", SqlDbType.SmallInt, rateUploadedVideoRequest.Score);
                    DBUtility.AddSqlParam(sqlParam, "@NumberOfScore", SqlDbType.BigInt, 1);
                    DBUtility.AddSqlParam(sqlParam, "@CreatedOn", SqlDbType.DateTime, DateTime.Now.ToString(ConfigurationManager.AppSettings["DateFormat"]));
                    DBUtility.AddSqlParam(sqlParam, "@LastModifiedDate", SqlDbType.DateTime, DateTime.Now.ToString(ConfigurationManager.AppSettings["DateFormat"]));
                    DBUtility.AddSqlParam(sqlParam, "@LastModifiedBy", SqlDbType.NVarChar, "System");
                    sqlStr = @"insert into Rating(Creator, UserVideo, Score, NumberOfScore, CreatedOn, LastModifiedDate, LastModifiedBy) 
                               values(@Creator, @UserVideo, @Score, @NumberOfScore, @CreatedOn, @LastModifiedDate, @LastModifiedBy)";
                    if (DBUtility.ExecuteNonQuery(sqlStr, sqlParam) != 1)
                    {
                        throw new ServiceException("Insert Rate Uploaded Video failed. VideoId: " + rateUploadedVideoRequest.VideoId + "; Creator: " + rateUploadedVideoRequest.CreatorId);
                    }
                    response.Result = true;
                    response.Message = "Rate Uploaded Video successed!";
                    return response;
                }
            }
            else
            {   
                throw new ServiceException("Not uploaded video. VideoId: " + rateUploadedVideoRequest.VideoId);
            }
        }
        //TODO: calculate rated score as average score: Score/NumberOfScore
        public GetRankingVideoResponse GetRankingVideo(GetRankingVideoRequest request)
        {
            GetRankingVideoResponse response = new GetRankingVideoResponse();
            List<SqlParameter> sqlParam = new List<SqlParameter>();
            string sqlStr = @"select 
                                    r.Id as VideoId,
                                    uuv.UserInfo as UserInfo, 
                                    uuv.UserActivity as UserActivity, 
                                    uuv.Status as Status, 
                                    uuv.SelfScore as SelfScore, 
                                    uuv.Url as Url, 
                                    uuv.ExtensionName as ExtensionName, 
                                    uuv.SentenceNumber as SentenceNumber, 
                                    r.Score/r.NumberOfScore as RatedScore
                            from UserUploadedVideo uuv
                            join Rating r on uuv.Id = r.UserVideo";

            if (request.Score > 0 && request.Ranking > 0)
            {
                DBUtility.AddSqlParam(sqlParam, "@Score", SqlDbType.BigInt, request.Score);
                DBUtility.AddSqlParam(sqlParam, "@Top", SqlDbType.BigInt, request.Ranking);                
                sqlStr += " " + @"where (r.Score/r.NumberOfScore) > @Score and r.Id in (SELECT TOP (@Top) Id FROM Rating order by (r.Score/r.NumberOfScore) desc)";
            }
            if (request.Score == null && request.Ranking > 0)
            {
                DBUtility.AddSqlParam(sqlParam, "@Top", SqlDbType.BigInt, request.Ranking);                
                sqlStr += " " + @"where r.Id in (SELECT TOP (@Top) Id  FROM Rating order by (r.Score/r.NumberOfScore) desc)";
            }
            if (request.Score > 0 && request.Ranking == null)
            {
                DBUtility.AddSqlParam(sqlParam, "@Score", SqlDbType.BigInt, request.Score);                
                sqlStr += " " + @"where (r.Score/r.NumberOfScore) > @Score order by (r.Score/r.NumberOfScore) desc";
            }
            if(request.Score ==null && request.Ranking ==null)
            {
                sqlStr += " " + @"order by (r.Score/r.NumberOfScore) desc";
            }
            var ds = DBUtility.ExecuteDataset(sqlStr, sqlParam);
            List<VideoInfo> videoInfos = new List<VideoInfo>();
            log.Debug("Getting ranking video now...");
            if (DBUtility.hasResult(ds))
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    VideoInfo videoInfo = new VideoInfo();
                    videoInfo.VideoId = Convert.ToInt32(dr["VideoId"]);
                    videoInfo.UserId = Convert.ToInt32(dr["UserInfo"]);
                    videoInfo.UserActivityId = Convert.ToInt32(dr["UserActivity"]);
                    videoInfo.Status = Convert.ToInt32(dr["Status"]);
                    videoInfo.SelfScore = Convert.ToInt32(dr["SelfScore"]);
                    videoInfo.ExtensionName = dr["ExtensionName"].ToString();
                    videoInfo.SentenceNumber = Convert.ToInt32(dr["SentenceNumber"]);
                    videoInfo.RatedScore = Convert.ToInt32(dr["RatedScore"]);
                    videoInfo.Url = dr["Url"].ToString();
                    videoInfos.Add(videoInfo);
                }
                response.VideoeList = videoInfos;
                return response;
            }
            else
            {
                throw new ServiceException(" Get ranking Video failed.");
            }
        }

        static byte[] GetBytes(string str)
        {
            byte[] bytes = new byte[str.Length * sizeof(char)];
            System.Buffer.BlockCopy(str.ToCharArray(), 0, bytes, 0, bytes.Length);
            return bytes;
        }

        static string GetString(byte[] bytes)
        {
            char[] chars = new char[bytes.Length / sizeof(char)];
            System.Buffer.BlockCopy(bytes, 0, chars, 0, bytes.Length);
            return new string(chars);
        }

        public UploadVideoResponse UploadVideo(UploadVideoRequest uploadVideoRequest)
        {
            UploadVideoResponse resp = new UploadVideoResponse();
            resp.Message = null;
            resp.UploadedVideoId = 0;
            int userActivityId = 0;

            List<SqlParameter> sqlParam = new List<SqlParameter>();
            string sqlStr = null;
            DBUtility.AddSqlParam(sqlParam, "@UserInfo", SqlDbType.BigInt, uploadVideoRequest.UserId);
            DBUtility.AddSqlParam(sqlParam, "@Activity", SqlDbType.BigInt, uploadVideoRequest.ActivityId);
            sqlStr = @"select Id from UserActivity where UserInfo = @UserInfo and Activity = @Activity";
            DataSet ds = DBUtility.ExecuteDataset(sqlStr, sqlParam);

            if (DBUtility.hasResult(ds))
            {
                String videoData = uploadVideoRequest.DataStreamStr;
                //(1) decode url 
                string urlDecodeData = HttpUtility.UrlDecode(videoData);

                //(2) decode base64
                byte[] binaryData = System.Convert.FromBase64String(urlDecodeData);

                String filePath = HttpContext.Current.Server.MapPath("/videos/");
                //string filePath = @"D:\SVN\UploadVideo\";
                string fileName = (uploadVideoRequest.UserId).ToString()
                                + "_" + (uploadVideoRequest.ActivityId).ToString()
                                + "_" + (uploadVideoRequest.SentenceNumber).ToString()
                                + "_" + DateTime.Now.Year + DateTime.Now.Month + DateTime.Now.Day
                                + DateTime.Now.Hour + DateTime.Now.Minute + DateTime.Now.Second
                                + "." + (uploadVideoRequest.ExtensionName).ToString();
                string path = Path.Combine(filePath, fileName);
                try
                {
                    using (FileStream targetStream = File.Create(path))
                    {
                        targetStream.Write(binaryData, 0, binaryData.Length);
                    }
                }
                catch (Exception e)
                {
                    resp.Message = e.ToString();
                }

                //find userActivity ID
                userActivityId = Convert.ToInt32(ds.Tables[0].Rows[0]["Id"]);
                sqlParam.Clear();
                DBUtility.AddSqlParam(sqlParam, "@UserInfo", SqlDbType.BigInt, uploadVideoRequest.UserId);
                DBUtility.AddSqlParam(sqlParam, "@UserActivity", SqlDbType.BigInt, userActivityId);
                DBUtility.AddSqlParam(sqlParam, "@Status", SqlDbType.BigInt, 0);
                DBUtility.AddSqlParam(sqlParam, "@SelfScore", SqlDbType.BigInt, uploadVideoRequest.SelfScore);
                DBUtility.AddSqlParam(sqlParam, "@Url", SqlDbType.NVarChar, path);
                DBUtility.AddSqlParam(sqlParam, "@SentenceNumber", SqlDbType.BigInt, uploadVideoRequest.SentenceNumber);
                DBUtility.AddSqlParam(sqlParam, "@ExtensionName", SqlDbType.NVarChar, uploadVideoRequest.ExtensionName);
                DBUtility.AddSqlParam(sqlParam, "@CreatedOn", SqlDbType.DateTime, DateTime.Now);
                DBUtility.AddSqlParam(sqlParam, "@LastModifiedDate", SqlDbType.DateTime, DateTime.Now);
                DBUtility.AddSqlParam(sqlParam, "@LastModifiedBy", SqlDbType.NVarChar, "System");
                sqlStr = @"insert into UserUploadedVideo
                           (UserInfo, UserActivity, Status, SelfScore, Url, ExtensionName, SentenceNumber, CreatedOn, LastModifiedDate, LastModifiedBy )
                           values(@UserInfo, @UserActivity, @Status, @SelfScore, @Url, @ExtensionName, @SentenceNumber, @CreatedOn, @LastModifiedDate, @LastModifiedBy)";
                if (DBUtility.ExecuteNonQuery(sqlStr, sqlParam) != 1)
                {
                    throw new ServiceException("User Upload Video failed. UserID: " + uploadVideoRequest.UserId);
                }
                //insert successful and find ID in UserUploadedVideo table
                sqlParam.Clear();
                DBUtility.AddSqlParam(sqlParam, "@UserInfo", SqlDbType.BigInt, uploadVideoRequest.UserId);
                DBUtility.AddSqlParam(sqlParam, "@Url", SqlDbType.NVarChar, path);
                sqlStr = @"select Id from UserUploadedVideo where UserInfo = @UserInfo and Url = @Url";
                ds = DBUtility.ExecuteDataset(sqlStr, sqlParam);
                if (DBUtility.hasResult(ds))
                {
                    resp.UploadedVideoId = Convert.ToInt32(ds.Tables[0].Rows[0]["Id"]);
                }
                resp.Message = "User Upload video inserted. UserID: " + uploadVideoRequest.UserId;
            }
            return resp;
        }

        //Return Comment List is null
        public VideoListResponse GetUploadedVideoList(VideoListRequest videoListRequest)
        {
            VideoListResponse resp = new VideoListResponse();
            List<SqlParameter> sqlParam = new List<SqlParameter>();

            string sqlStr = @"select 
                                    uuv.Id as VideoId,
                                    uuv.UserInfo as UserId,
                                    uuv.UserActivity as UserActivityId,
                                    uuv.Status as Status,
                                    uuv.SelfScore as SelfScore,
                                    uuv.ExtensionName as ExtensionName,
                                    uuv.SentenceNumber as SentenceNumber,
                                    uuv.Url as Url                     
                                    from UserUploadedVideo uuv";
            if (videoListRequest.UserId > 0)
            {
                if (videoListRequest.SentenceNumber > 0)
                {
                    if (videoListRequest.ActivityId > 0)
                    {//activityId and SentenceNumber
                        DBUtility.AddSqlParam(sqlParam, "@Activity", SqlDbType.BigInt, videoListRequest.ActivityId);
                        DBUtility.AddSqlParam(sqlParam, "@UserInfo", SqlDbType.BigInt, videoListRequest.UserId);
                        DBUtility.AddSqlParam(sqlParam, "@SentenceNumber", SqlDbType.BigInt, videoListRequest.SentenceNumber);
                        sqlStr += " " + @"join UserActivity ua on uuv.UserActivity = ua.Id where uuv.UserInfo = @UserInfo and uuv.SentenceNumber = @SentenceNumber and  ua.Userinfo = @UserInfo and ua.Activity = @Activity";
                        resp.VideoList = GetVideoList(sqlStr, sqlParam);
                    }
                    else
                    {//only sentenceNumber
                        DBUtility.AddSqlParam(sqlParam, "@UserInfo", SqlDbType.BigInt, videoListRequest.UserId);
                        DBUtility.AddSqlParam(sqlParam, "@SentenceNumber", SqlDbType.BigInt, videoListRequest.SentenceNumber);
                        sqlStr += " " + @"where uuv.UserInfo = @UserInfo and uuv.SentenceNumber = @SentenceNumber";
                        resp.VideoList = GetVideoList(sqlStr, sqlParam);
                    }
                }
                else
                {
                    if (videoListRequest.ActivityId > 0)
                    {//only ActivityID
                        DBUtility.AddSqlParam(sqlParam, "@Activity", SqlDbType.BigInt, videoListRequest.ActivityId);
                        DBUtility.AddSqlParam(sqlParam, "@UserInfo", SqlDbType.BigInt, videoListRequest.UserId);
                        sqlStr += " " + @"join UserActivity ua on uuv.UserActivity = ua.Id 
                                where uuv.UserInfo = @UserInfo and ua.Userinfo = @UserInfo and ua.Activity = @Activity";
                        resp.VideoList = GetVideoList(sqlStr, sqlParam);
                    }
                    else
                    {//Only UserId
                        DBUtility.AddSqlParam(sqlParam, "@UserInfo", SqlDbType.BigInt, videoListRequest.UserId);
                        sqlStr += " " + @"where uuv.UserInfo = @UserInfo";
                        resp.VideoList = GetVideoList(sqlStr, sqlParam);
                    }
                }
                return resp;
            }
            else
            {
                throw new ServiceException("GetUploadVideoList fail. UserId: " + videoListRequest.UserId);
            }
        }

        private List<VideoInfo> GetVideoList(string sqlStr, List<SqlParameter> sqlParam)
        {
            List<VideoInfo> videoList = new List<VideoInfo>();
            DataSet ds = DBUtility.ExecuteDataset(sqlStr, sqlParam);
            if (DBUtility.hasResult(ds))
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    VideoInfo info = new VideoInfo();
                    info.VideoId = Convert.ToInt32(dr["VideoId"]);
                    info.UserId = Convert.ToInt32(dr["UserId"]);
                    info.UserActivityId = Convert.ToInt32(dr["UserActivityId"]);
                    info.Status = Convert.ToInt32(dr["Status"]);
                    info.SelfScore = Convert.ToInt32(dr["SelfScore"]);
                    info.ExtensionName = dr["ExtensionName"].ToString();
                    info.SentenceNumber = Convert.ToInt32(dr["SentenceNumber"]);
                    info.Url = dr["Url"].ToString();
                    videoList.Add(info);
                }
            }
            return videoList;
        }

        private List<CommentInfo> GetUploadedVdeoCommentList(int creator, int userVideo)
        {
            List<CommentInfo> commentList = new List<CommentInfo>();
            List<SqlParameter> sqlParam = new List<SqlParameter>();
            string sqlString = null;
            DBUtility.AddSqlParam(sqlParam, "@Creator", SqlDbType.BigInt, creator);
            DBUtility.AddSqlParam(sqlParam, "@UserVideo", SqlDbType.BigInt, userVideo);
            sqlString = @"select 
                                ui.Name as UserName,
                                c.ToComment as ToComment,
                                c.UserVideo as UserVideo,
                                c.Content as Content 
                                from Comment c
                                join UserInfo ui on c.Creator = ui.Id
                                where UserVideo = @UserVideo and c.Creator = @Creator"; ;
            DataSet commentSet = DBUtility.ExecuteDataset(sqlString, sqlParam);
            if (DBUtility.hasResult(commentSet))
            {
                foreach (DataRow comm in commentSet.Tables[0].Rows)
                {
                    CommentInfo comment = new CommentInfo();
                    if (comm["ToComment"].ToString().Length > 0)
                    {
                        comment.ToCommentId = Convert.ToInt32(comm["ToComment"]);
                    }
                    else
                    {
                        comment.ToCommentId = null;
                    }
                    comment.UserName = comm["UserName"].ToString();
                    comment.UserVideoId = Convert.ToInt32(comm["UserVideo"]);
                    comment.Content = comm["Content"].ToString();
                    commentList.Add(comment);
                }
            }
            return commentList;
        }

        public GetUploadedVideoResponse GetUploadedVideoById(GetUploadedVideoRequest getUploadedVideoRequest)
        {
            GetUploadedVideoResponse resp = new GetUploadedVideoResponse();
            List<SqlParameter> sqlParam = new List<SqlParameter>();
            string sqlStr = null;
            DBUtility.AddSqlParam(sqlParam, "@Id", SqlDbType.BigInt, getUploadedVideoRequest.VideoId);
            DBUtility.AddSqlParam(sqlParam, "@UserInfo", SqlDbType.BigInt, getUploadedVideoRequest.UserId);
            sqlStr = @"select Id, UserInfo, UserActivity, Status, SelfScore, Url, ExtensionName, SentenceNumber 
                        from UserUploadedVideo 
                        where Id = @Id and UserInfo = @UserInfo";
            DataSet ds = DBUtility.ExecuteDataset(sqlStr, sqlParam);
            if (DBUtility.hasResult(ds))
            {
                VideoInfo info = new VideoInfo();
                DataRow dr = ds.Tables[0].Rows[0];
                info.UserId = Convert.ToInt32(dr["UserInfo"]);
                info.UserActivityId = Convert.ToInt32(dr["UserActivity"]);
                info.Status = Convert.ToInt32(dr["Status"]);
                info.Url = dr["Url"].ToString();
                info.ExtensionName = dr["ExtensionName"].ToString();
                info.SentenceNumber = Convert.ToInt32(dr["SentenceNumber"]);

                //Retrieve comment list for each Uid and Vid
                int creator = Convert.ToInt32(dr["UserInfo"]);
                int userVideoId = Convert.ToInt32(dr["Id"]);
                info.CommentList = GetUploadedVdeoCommentList(creator, userVideoId);
                resp.UploadedVideoInfo = info;
            }
            return resp;
        }
    }
}